/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import ftpdownload.LogFile;
import pojo.FtpInfo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;

/**
 *
 * @author ahmed
 */
public class FtpInfoCrud {

    private Connection conn = null;
    private PreparedStatement stmt = null;

    public FtpInfoCrud() {

        try {
            //rigister driver
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            //obtain connection
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dimensionproject", "root", "root");


        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }

    }

    public boolean update(FtpInfo newFtpInfo, int id) {
        try {
            boolean first = false;
            String updatetxt = "";
            if (newFtpInfo.getHost() != null) {
                first = true;
                updatetxt = "host='" + newFtpInfo.getHost() + "'";
            }
            if (newFtpInfo.getRemoteDir() != null) {
                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "remote_dir='" + newFtpInfo.getRemoteDir() + "'";
            }
            if (newFtpInfo.getFileName() != null) {
                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "file_name='" + newFtpInfo.getFileName() + "'";
            }
            if (newFtpInfo.getFileExtension() != null) {
                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "file_extension='" + newFtpInfo.getFileExtension() + "'";
            }
            if (newFtpInfo.getUserName() != null) {

                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "user_name='" + newFtpInfo.getUserName() + "'";
            }
            if (newFtpInfo.getPassword() != null) {

                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "password='" + newFtpInfo.getPassword() + "'";
            }
            if (newFtpInfo.getDateMod() != null) {

                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "date_mod='" + newFtpInfo.getDateMod() + "'";
            }
            if (newFtpInfo.getLocalDir() != null) {

                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "local_dir='" + newFtpInfo.getLocalDir() + "'";
            }

            stmt = conn.prepareStatement("update ftp_info set " + updatetxt + "where ftp_id=? ;");
            stmt.setInt(1, id);
            return stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean add(FtpInfo newFtpInfo) {

        try {
            stmt = conn.prepareStatement("insert into ftp_info values(?,?,?,?,?,?,?,?,?)");
            stmt.setInt(1, newFtpInfo.getFtpId());
            stmt.setString(2, newFtpInfo.getHost());
            stmt.setString(3, newFtpInfo.getRemoteDir());
            stmt.setString(4, newFtpInfo.getFileName());
            stmt.setString(5, newFtpInfo.getFileExtension());
            stmt.setString(6, newFtpInfo.getUserName());
            stmt.setString(7, newFtpInfo.getPassword());
            stmt.setString(8, newFtpInfo.getDateMod());
            stmt.setString(9, newFtpInfo.getLocalDir());
            stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;

    }

    //retreive all ftp data by id
    public FtpInfo selectById(int id) {
        FtpInfo ftpInfo = null;
        try {
            stmt = conn.prepareStatement("SELECT * FROM ftp_info WHERE ftp_id=?");
            stmt.setInt(1, id);
            ResultSet resultSet = stmt.executeQuery();
            if (resultSet.next()) {
                ftpInfo = new FtpInfo(id);
                ftpInfo.setHost(resultSet.getString(2));
                ftpInfo.setRemoteDir(resultSet.getString(3));
                ftpInfo.setFileName(resultSet.getString(4));
                ftpInfo.setFileExtension(resultSet.getString(5));
                ftpInfo.setUserName(resultSet.getString(6));
                ftpInfo.setPassword(resultSet.getString(7));
                ftpInfo.setDateMod(resultSet.getString(8));
                ftpInfo.setLocalDir(resultSet.getString(9));
                return ftpInfo;
            }
            else{
                LogFile.log(" System ", " did not find any Information for FTP with ID  ["+id+"]");
                return null;
            }

        } catch (SQLException e) {
            LogFile.log(" System ", " encountered the following Exception while trying to retreive data of FTP with ID : ["+id+"], the Exception is : ["+e+"]");
            e.printStackTrace();
        }
        return null;
    }

    public boolean deleteById(int id) {

        try {
            stmt = conn.prepareStatement("delete from ftp_info where ftp_id=?");
            stmt.setInt(1, id);
            return stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;

    }

    public static void main(String[] args) {
//        FtpInfo fi=new FtpInfo(1, "hhh", "rrr", "exe", "hhh", "jhh", new Date(1990, 3, 1), "ll");
//       new FtpInfoCrud().add(fi);
//        System.out.println("Add ");
    }
}
